Embodiments of the present invention relate in general to databases, and in particular to techniques for aiding a user in tuning database performance by recommending alternative SQL execution plans.
Generally speaking, a SQL execution plan is a series of operations that indicate to a database server how to physically execute a SQL statement. A number of different execution plans can be used to execute any given statement. Consider, for instance, a SQL query that selects from an employee table all employees residing in the state of California (e.g., SELECT * FROM EMPLOYEES WHERE STATE=“CA”). One execution plan for this query can read all of the rows in the employee table and select the rows that fulfill the condition STATE=“CA” (referred to as a full table scan). Another execution plan can use a database index on the STATE column (if such an index exists) to find entries in the index data structure where STATE=“CA” (thereby possibly avoiding a scan of the entire table). A component of the database server, typically called the optimizer, is responsible for choosing the optimal execution plan for a SQL statement given a particular database context.
In some cases, the optimizer may choose a new execution plan for a SQL statement that has a slower response time than a previously used execution plan. This may occur, for example, if the statistics for one or more database objects accessed by the SQL statement become stale or missing, or other reasons. In these scenarios, it may be desirable to have the optimizer return to using a previous (e.g., historical) execution plan for the statement. Unfortunately, there is currently no automated mechanism for aiding a user (e.g., a database administrator (DBA)) in identifying historical execution plans for a given SQL statement and determining whether to have the optimizer return to one of those historical plans.